package com.hj.controller;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.alibaba.fastjson.JSON;
import com.hj.entity.CompanyInfo;
import com.hj.service.CompanyInfoService;
import com.hj.utils.Json;


@Controller
@RequestMapping("/excel")
public class ExcelOutControler {
	/**
	 * excel 导出
	 * */
	@Autowired
	private CompanyInfoService companyinfoservice;
	@SuppressWarnings("deprecation")
	@RequestMapping("/outExcel")
	public void excelGoods(CompanyInfo m, HttpServletRequest req,HttpServletResponse resp){
		resp.setContentType("text/html;charset=UTF-8");
		List<CompanyInfo> cp=companyinfoservice.search(m);
		HSSFWorkbook hw=new HSSFWorkbook();
		Json j=new Json();
		try {
			FileOutputStream file=new FileOutputStream("d:\\"+"Company.xlsx");
			HSSFSheet hs= hw.createSheet("Sheet1");			
			//设置字体样式
			HSSFCellStyle style = hw.createCellStyle();   
	        HSSFFont font = hw.createFont(); 
	        font.setFontHeightInPoints((short) 14);// 设置字体大小
			style.setFont(font);
			
			//应用样式
			hs.setDefaultColumnStyle(0, style);
			hs.setDefaultColumnStyle(1, style);
			hs.setDefaultColumnStyle(2, style);
			hs.setDefaultColumnStyle(3, style);
			hs.setDefaultColumnStyle(4, style);
			hs.setDefaultColumnStyle(5, style);
			hs.setDefaultColumnStyle(6, style);
			hs.setDefaultColumnStyle(7, style);
			//设置当前sheet页的所有列宽和高
			hs.setDefaultColumnWidth(40);
			hs.setDefaultRowHeightInPoints(20);
			//设置商品名称列的宽度
			hs.setColumnWidth(1,600*20);
			//创建下标为0第一行标题行和列值
			HSSFRow hd=hs.createRow(0);
			HSSFCell hc0=hd.createCell(0);
			hc0.setCellValue("400号码");
			HSSFCell hc1=hd.createCell(1);
			hc1.setCellValue("密码");
			HSSFCell hc2=hd.createCell(2);
			hc2.setCellValue("公司名");
			HSSFCell hc3=hd.createCell(3);
			hc3.setCellValue("联系人");
			HSSFCell hc4=hd.createCell(4);
			hc4.setCellValue("电话");
			HSSFCell hc5=hd.createCell(5);
			hc5.setCellValue("邮箱");
			HSSFCell hc6=hd.createCell(6);
			hc6.setCellValue("地址");
			HSSFCell hc7=hd.createCell(7);
			hc7.setCellValue("创建时间");
			HSSFCell hc8=hd.createCell(8);
			hc8.setCellValue("更新时间");
			HSSFCell hc9=hd.createCell(9);
			hc9.setCellValue("合同期限");
			HSSFCell hc10=hd.createCell(10);
			hc10.setCellValue("生效时间");
			HSSFCell hc11=hd.createCell(11);
			hc11.setCellValue("到期时间");
			HSSFCell hc12=hd.createCell(12);
			hc12.setCellValue("余额");
			HSSFCell hc13=hd.createCell(13);
			hc13.setCellValue("月最低消费");
			HSSFCell hc14=hd.createCell(14);
			hc14.setCellValue("指导价格");
			HSSFCell hc15=hd.createCell(15);
			hc15.setCellValue("月基础");
			HSSFCell hc16=hd.createCell(16);
			hc16.setCellValue("月赠送时长");
			HSSFCell hc17=hd.createCell(17);
			hc17.setCellValue("月赠送时长余额");
			HSSFCell hc18=hd.createCell(18);
			hc18.setCellValue("charge_cost");
			HSSFCell hc19=hd.createCell(19);
			hc19.setCellValue("real_name");
			HSSFCell hc20=hd.createCell(20);
			hc20.setCellValue("地点");
			HSSFCell hc21=hd.createCell(21);
			hc21.setCellValue("类别");
			HSSFCell hc22=hd.createCell(22);
			hc22.setCellValue("状态");
			HSSFCell hc23=hd.createCell(23);
			hc23.setCellValue("agency");
			HSSFCell hc24=hd.createCell(24);
			hc24.setCellValue("sale_manager");
			HSSFCell hc25=hd.createCell(25);
			hc25.setCellValue("sale_mobile");
			HSSFCell hc26=hd.createCell(26);
			hc26.setCellValue("commission");
			HSSFCell hc27=hd.createCell(27);
			hc27.setCellValue("memo");
		
			
			for(int i=1;i<=cp.size();i++){
				HSSFRow hr=hs.createRow(i);
				HSSFCell hce0=hr.createCell(0);
				hce0.setCellValue(cp.get(i-1).getCallee_number());
				HSSFCell hce1=hr.createCell(1);
				hce1.setCellValue(cp.get(i-1).getAuth_code());
				HSSFCell hce2=hr.createCell(2);
				hce2.setCellValue(cp.get(i-1).getCompany_name());
				HSSFCell hce3=hr.createCell(3);
				hce3.setCellValue(cp.get(i-1).getContactor());
				HSSFCell hce4=hr.createCell(4);
				hce4.setCellValue(cp.get(i-1).getMobile());
				HSSFCell hce5=hr.createCell(5);
				hce5.setCellValue(cp.get(i-1).getMail());
				HSSFCell hce6=hr.createCell(6);
				hce6.setCellValue(cp.get(i-1).getAddress());
				HSSFCell hce7=hr.createCell(7);
				if(cp.get(i-1).getCreate_time()==null){
					hce7.setCellValue("");	
				}else{
					hce7.setCellValue(cp.get(i-1).getCreate_time().toLocaleString());
				}
				HSSFCell hce8=hr.createCell(8);
				if(cp.get(i-1).getUpdate_time()==null){
					hce8.setCellValue("");	
				}else{
					hce8.setCellValue(cp.get(i-1).getUpdate_time().toLocaleString());
				}				
				HSSFCell hce9=hr.createCell(9);
				hce9.setCellValue(cp.get(i-1).getContract_month());
				HSSFCell hce10=hr.createCell(10);
				if(cp.get(i-1).getStart_date()==null){
					hce10.setCellValue("");	
				}else{
					hce10.setCellValue(cp.get(i-1).getStart_date().toLocaleString());
				}
				HSSFCell hce11=hr.createCell(11);
				if(cp.get(i-1).getExpire_date()==null){
					hce11.setCellValue("");
				}else{
					hce11.setCellValue(cp.get(i-1).getExpire_date().toLocaleString());
				}
				HSSFCell hce12=hr.createCell(12);
				hce12.setCellValue(cp.get(i-1).getBalance());
				HSSFCell hce13=hr.createCell(13);
				hce13.setCellValue(cp.get(i-1).getMonth_fee());
				HSSFCell hce14=hr.createCell(14);
				hce14.setCellValue(cp.get(i-1).getGuide_price());
				HSSFCell hce15=hr.createCell(15);
				hce15.setCellValue(cp.get(i-1).getMonth_base());
				HSSFCell hce16=hr.createCell(16);
				hce16.setCellValue(cp.get(i-1).getMonth_gift());
				HSSFCell hce17=hr.createCell(17);
				hce17.setCellValue(cp.get(i-1).getMonth_gift_rest());
				HSSFCell hce18=hr.createCell(18);
				hce18.setCellValue(cp.get(i-1).getCharge_cost());
				HSSFCell hce19=hr.createCell(19);
				hce19.setCellValue(cp.get(i-1).getReal_name());
				HSSFCell hce20=hr.createCell(20);
				hce20.setCellValue(cp.get(i-1).getArea());
				HSSFCell hce21=hr.createCell(21);
				hce21.setCellValue(cp.get(i-1).getCategory());
				HSSFCell hce22=hr.createCell(22);
				hce22.setCellValue(cp.get(i-1).getStatus());
				HSSFCell hce23=hr.createCell(23);
				hce23.setCellValue(cp.get(i-1).getAgency());
				HSSFCell hce24=hr.createCell(24);
				hce24.setCellValue(cp.get(i-1).getSale_manager());
				HSSFCell hce25=hr.createCell(25);
				hce25.setCellValue(cp.get(i-1).getSale_mobile());
				HSSFCell hce26=hr.createCell(26);
				hce26.setCellValue(cp.get(i-1).getCommission());
				HSSFCell hce27=hr.createCell(27);
				hce27.setCellValue(cp.get(i-1).getMemo());
			}
			hw.write(file);
			file.close();
			j.setMsg("导出成功!请在D:/盘符下查看");
		} catch (IOException e) {
			j.setMsg("导出失败!");
			e.printStackTrace();
		}finally{
			try {
				PrintWriter pw = resp.getWriter();
				String st=JSON.toJSONString(j);
				pw.write(st);
				pw.flush();
				pw.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			
		}
	}
}
